﻿using Common;
using DBUtility;
using Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BaseDal
{
    public class StaffDal
    {
        private static readonly Lazy<StaffDal> dal = new Lazy<StaffDal>(() => new StaffDal());
        public static StaffDal Instance { get; } = dal.Value;

        private StaffDal()
        { }

        private const string tableName = "Staff";

        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public bool IsExist(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + tableName);
            strSql.Append(" where IsDelete=0 " + strWhere);
            return SqlHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 查询SQL语句
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public StringBuilder GetSelectSql(string strWhere = "")
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT t1.ID,t1.Name,t1.Gender,t1.Age,t1.PhoneNumber,t1.BirthDate,t1.Education,t1.PostId,t2.PostName,");
            sb.Append("t1.Title,t1.WorkYears,t1.JobNum,t1.StaffState,t1.Certificate,t1.CreateTime,t1.CreateUser,t1.UpdateTime,t1.UpdateUser ");
            sb.Append("FROM " + tableName + " t1 ");
            sb.Append("Left join Post t2 on t1.PostId=t2.ID ");
            sb.Append(" Where t1.IsDelete=0 ");

            if (!string.IsNullOrEmpty(strWhere))
            {
                sb.Append(strWhere);
            }

            return sb;
        }

        /// <summary>
        /// 查询分页数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetPageList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            string countSql = PageHelper.CreateCountingSql(sb.ToString());
            recordCount = Convert.ToInt32(SqlHelper.GetSingle(countSql));
            string pageSql = PageHelper.CreatePageSql(recordCount, pageSize, pageIndex, sb.ToString(), filedOrder);
            DataSet ds = SqlHelper.Query(pageSql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 查询数据列表
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public List<Staff> GetList(string strWhere)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            DataSet ds = SqlHelper.Query(sb.ToString());
            return CommonOperation.ConvertDataTableToModelList<Staff>(ds.Tables[0]);
        }

        /// <summary>
        /// 根据id查询实体类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Staff GetModelById(int id)
        {
            StringBuilder sb = GetSelectSql("and t1.ID=@id");
            SqlParameter[] parameters =
            {
                new SqlParameter("@id",id)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return CommonOperation.DataTableToModel<Staff>(ds.Tables[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 新增员工
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddStaff(Staff model)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO " + tableName + "(");
            sb.Append("Name,Gender,PhoneNumber,BirthDate,Age,Education,PostId,Title,WorkYears,");
            sb.Append("JobNum,Certificate,CreateTime,CreateUser)");
            sb.Append(" VALUES(");
            sb.Append("@name,@gender,@phoneNumber,@birthDate,@age,@education,@postId,@title,@workYears,");
            sb.Append("@jobNum,@certificate,@createTime,@createUser) ");
            sb.Append("SELECT  @@IDENTITY  as  'id'");
            SqlParameter[] parameters =
           {
                new SqlParameter("@name",model.Name),
                new SqlParameter("@gender",model.Gender),
                new SqlParameter("@phoneNumber",model.PhoneNumber),
                new SqlParameter("@birthDate",model.BirthDate.HasValue ? (object)model.BirthDate.Value : DBNull.Value),
                new SqlParameter("@age",model.Age),
                new SqlParameter("@education",model.Education),
                new SqlParameter("@postId",model.PostId),
                new SqlParameter("@title",model.Title),
                new SqlParameter("@workYears",model.WorkYears),
                new SqlParameter("@jobNum",model.JobNum),
                new SqlParameter("@certificate",model.Certificate),
                new SqlParameter("@createTime",model.CreateTime),
                new SqlParameter("@createUser",model.CreateUser)
            };
            object result = SqlHelper.ExecuteReturnIdentity(sb.ToString(), parameters);
            if (int.TryParse(result.ToString(), out int id))
            {
                model.ID = id;
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 修改人员
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateStaff(Staff model)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Update " + tableName + " SET ");
            sb.Append("Name=@name,");
            sb.Append("Gender=@gender,");
            sb.Append("PhoneNumber=@phoneNumber,");
            sb.Append("BirthDate=@birthDate,");
            sb.Append("Age=@age,");
            sb.Append("Education=@education,");
            sb.Append("PostId=@postId,");
            sb.Append("Title=@title,");
            sb.Append("WorkYears=@workYears,");
            sb.Append("JobNum=@jobNum,");
            sb.Append("Certificate=@certificate,");
            sb.Append("UpdateTime=@updateTime,");
            sb.Append("UpdateUser=@updateUser ");
            sb.Append("WHERE ID=@id");
            SqlParameter[] parameters = {
                new SqlParameter("@name",model.Name),
                new SqlParameter("@gender",model.Gender),
                new SqlParameter("@phoneNumber",model.PhoneNumber),
                new SqlParameter("@birthDate",model.BirthDate.HasValue ? (object)model.BirthDate.Value : DBNull.Value),
                new SqlParameter("@age",model.Age),
                new SqlParameter("@education",model.Education),
                new SqlParameter("@postId",model.PostId),
                new SqlParameter("@title",model.Title),
                new SqlParameter("@workYears",model.WorkYears),
                new SqlParameter("@jobNum",model.JobNum),
                new SqlParameter("@certificate",model.Certificate),
                new SqlParameter("@updateTime",model.UpdateTime),
                new SqlParameter("@updateUser",model.UpdateUser),
                new SqlParameter("@id",model.ID)
            };
            int result = SqlHelper.ExecuteSql(sb.ToString(), parameters);
            return result > 0;
        }

        /// <summary>
        /// 删除人员
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="userName"></param>
        /// <returns></returns>
        public bool DeleteStaff(string ids, string userName)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" update " + tableName + " set ");
            strSql.Append(" IsDelete=1,DeleteTime=GetDate(),DeleteUser='" + userName + "' ");
            strSql.Append(" Where ID in(" + ids + ")");
            int result = SqlHelper.ExecuteSql(strSql.ToString());
            return result > 0;
        }
    }
}